\documentclass[a4paper,10pt]{article}
\usepackage[utf8]{inputenc}
\usepackage{graphicx}
\usepackage[francais]{babel}
\usepackage{amsmath}
\usepackage{amssymb}
\usepackage{mathrsfs}
\usepackage{wrapfig}
\usepackage[vlined,lined,linesnumbered,boxed,french,longend]{algorithm2e}
\usepackage{xcolor}
\usepackage[top=2.5cm, bottom=2.5cm, left=2cm, right=2cm]{geometry}
\usepackage{lmodern}
\usepackage{enumerate}
\usepackage{verbatim}
\usepackage{multirow}
\usepackage{multicol}
\usepackage{tikz}
\usepackage{listings}
\usetikzlibrary{arrows,automata,shapes}

\lstset{language=SQL, frame=shadowbox}

%partie concernant la gestion des entêtes
\usepackage{fancyhdr}
\lhead{\bsc{Projet Système de Gestion de Base de Données}}
\rhead{Gestion du Foyer de l’\bsc{Enseirb-Matmeca}}
\renewcommand{\headrulewidth}{1px}
\lfoot{ \bsc{Enseirb-Matmeca}}
\rfoot{ \bsc{Informatique-I2}}
\renewcommand{\footrulewidth}{1px}
\pagestyle{fancy}
%fin
\begin{document}

\clearpage
\vspace*{\stretch{1}}
\begin{center}
{\huge Projet Système de Gestion de Base de Données}\\
{\footnotesize Gestion du Foyer de l’\bsc{Enseirb-Matmeca}}\\
~\\
{\large Stephane \bsc{Castelli} \& Kévin \bsc{Gallienne} \& Sami \bsc{Lachdhaf} }
\end{center}
\vspace*{\stretch{1}}

\begin{center}
\today
\end{center}

\newpage

\newpage
\tableofcontents
\newpage

\section*{Introduction}
\addcontentsline{toc}{section}{Introduction}

	Un projet informatique est souvent amené à manipuler un grand nombre de données interdépendantes. Par soucis d'ergonomie et d'optimisation, il est souvent pertinent d'utiliser un système de gestion de base de données. Les données sont alors stockées dans des tables qui sont elles-même stockées dans une base.\\
	
	Chacune des tables doit représenter une entité propre possédant des attributs. Elles sont reliées les unes aux autres par des relations qui permettent la manipulation des données qui dépendent les unes des autres. Si ce système permet de faciliter l'exploitation des données, il incombe au programmeur de concevoir la base de manière à limiter au maximum la redondance d'informations.\\
	
	Ce projet se propose donc de réaliser une base de données représentant la gestion du foyer de l'\bsc{ENSEIRB-MATMECA}. Cette gestion implique principalement des adhérents, des événements, des jeux et des livres. La conception de cette base de données passe premièrement par une modélisation conceptuelle et relationnelle. Puis dans un second temps, la manipulation des données implique la mise en place de requêtes qui permettront la consultation de la base. Enfin, pour faciliter l'interaction avec l'utilisateur, une interface sera mise en place.

\section{Modélisation de la base de données}

\subsection{Hypothèses}

\subsubsection{Tables de données}

Pour définir l'ensemble des tables qui vont composer la base, il est nécessaire de définir avec précision les différentes entités qui interviennent ainsi que les différents attributs qui les caractérisent. Les attributs pouvant être déduits des autres ne doivent pas apparaître. Cette analyse permet d'avoir une vision globale des données stockées.

\begin{wrapfigure}[5]{l}{3cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{personne}\\
\hline
idPersonne\\
nomPersonne\\
prenomPersonne\\
\hline
\end{tabular}
\end{wrapfigure}
~

Un foyer permet principalement à des personnes de se regrouper pour discuter, lire ou encore jouer.
Ces personnes se caractérisent essentiellement par leur nom et leur prénom, la table \texttt{personne}, qui stockera les personnes, est donc composée des trois champs \texttt{idPersonne}, \texttt{nomPersonne} et \texttt{prenomPersonne}.

\begin{wrapfigure}[5]{r}{3cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{eleve}\\
\hline
idPersonne\\
login\\
promotion\\
filiere\\
\hline
\end{tabular}
\end{wrapfigure}
~\\

Cependant ces personnes, n'ont pas toutes le même statut. En effet, certaines personnes sont des élèves de l'école. Une telle personne possède alors, en plus des attributs d'une personne, un login, une filière et une promotion. La table \texttt{eleve} est donc composée des quatre champs \texttt{idPersonne}, \texttt{login}, \texttt{promotion} et \texttt{filière}.\\

\begin{wrapfigure}[6]{l}{3cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{adherent}\\
\hline
login\\
idAdherent\\
dateAdhesion\\
dateSeparation\\
\hline
\end{tabular}
\end{wrapfigure}
~

De plus, tous les élèves ne participent pas à la vie du foyer. Seul les adhérents peuvent accéder aux services que fournit le foyer. C'est pourquoi un adhérent possède, en plus des attributs d'un élève, une date d'adhésion et une date de séparation. La table \texttt{adherent} est donc composée des quatre champs \texttt{login}, \texttt{idAdherent}, \texttt{dateAdhesion} et \texttt{dateSeparation}.

\begin{wrapfigure}[5]{r}{3cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{bureau}\\
\hline
idAdherent\\
idBureau\\
dateEntree\\
dateSortie\\
\hline
\end{tabular}
\end{wrapfigure}
~\\

Enfin, le foyer est dirigé par un nombre restreint d'adhérent qu'on appelle le bureau. Un membre du bureau possède, en plus des attributs d'un adhérent, une date d'entrée et une date de sortie. La table \texttt{bureau} est donc composée des quatre champs \texttt{idAdherent}, \texttt{idBureau}, \texttt{dateEntree} et \texttt{dateSortie}.\\

\begin{wrapfigure}[6]{l}{3cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{livre}\\
\hline
ISBN\\
empruntable\\
titre\\
dateAchat\\
prixAchat\\
\hline
\end{tabular}
\end{wrapfigure}
~\\

Le foyer met à la disposition des adhérents des livres qui ont été écrits par des personnes. Chaque livre se caractérise par un numéro ISBN, un titre, une date d'achat, un prix d'achat, et est désigné comme empruntable ou non. La table \texttt{livre} est donc composée des cinq champs \texttt{ISBN}, \texttt{empruntable}, \texttt{titre}, \texttt{dateAchat} et \texttt{prixAchat}.\\

\begin{wrapfigure}[2]{l}{3cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{editeur}\\
\hline
idEditeur\\
nom\\
\hline
\end{tabular}
\end{wrapfigure}

Les livres ont été édités par des éditeurs, qui se démarquent uniquement par leur nom. La table \texttt{editeur} est donc composée des deux champs \texttt{idEditeur} et \texttt{nomEditeur}.

\begin{wrapfigure}[4]{r}{3cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{evenement}\\
\hline
idEvenement\\
dateEvenement\\
lieu\\
\hline
\end{tabular}
\end{wrapfigure}
~\\

Afin de maintenir la vie du foyer et ainsi divertir les adhérents, des événements sont organisés. Un événement est planifié à une date et un lieu donné, la table \texttt{evenement} est donc composée des trois champs \texttt{idEvenement}, \texttt{dateEvenement} et \texttt{lieu}.

\begin{wrapfigure}[7]{l}{3cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{jeux}\\
\hline
nomJeu\\
etat\\
dateAchat\\
prixAchat\\
joueursMin\\
joueursMax\\
\hline
\end{tabular}
\end{wrapfigure}
~\\

Enfin chaque événement est organisé autour de jeux de société. Ces jeux possèdent un nom, un état, une date d'achat, un prix d'achat, et un nombre de joueurs minimum et un nombre maximum de joueurs. La table \texttt{jeu} est donc composée de six champs \texttt{nomJeu}, \texttt{etat}, \texttt{dateAchat}, \texttt{prixAchat}, \texttt{joueursMin} et \texttt{joueursMax}.\\

\subsubsection{Tables de relations}

Chacune de ces tables prises séparément présente un intérêt très restreint. Une telle organisation prend tout son sens lorsque ces tables sont mises en relation les une avec les autres. Ces liaisons peuvent être purement symboliques ou bien prendre aussi la forme de tables dont les attributs définissent son contexte précisément.

\begin{wrapfigure}[4]{l}{2cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{ecriture}\\
\hline
idPersonne\\
ISBN\\
\hline
\end{tabular}
\end{wrapfigure}
~

Par exemple, comme évoqué précédemment, les livres ont été écrits par une ou plusieurs personnes. Il faut donc pouvoir relier un livre à un ou plusieurs individus. On utilise pour cela la table \texttt{ecriture} composée des deux champs \texttt{idPersonne} et \texttt{ISBN}.

\begin{wrapfigure}[3]{r}{2cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{edite}\\
\hline
idEditeur\\
ISBN\\
\hline
\end{tabular}
\end{wrapfigure}
~

Comme pour les auteurs, un livre a, en outre, un ou plusieurs éditeurs. La table \texttt{edite} est une table représentant la relation entre un livre et ses éditeurs potentiels. Pour établir cette liaison, la table est composée des deux champs \texttt{idEditeur} et \texttt{ISBN}.

\begin{wrapfigure}[6]{l}{4cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{emprunt}\\
\hline
idAdherent\\
ISBN\\
dateEmprunt\\
dateRetour\\
\hline
\end{tabular}
\end{wrapfigure}
~

Enfin un livre a pour vocation première, au delà d'être consulté sur place, de pouvoir être emprunté par les adhérents du foyer. La table \texttt{emprunt} est une table représentant la relation entre un livre et ses emprunteurs. De plus, pour préciser un emprunt, elle stipule les date d'emprunt et de retour du livre, elle est donc composée des quatre champs \texttt{idAdherent}, \texttt{ISBN}, \texttt{dateEmprunt} et \texttt{dateRetour}.\\

\begin{wrapfigure}[5]{r}{3cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{participe}\\
\hline
idAdherent\\
idEvenement\\
\hline
\end{tabular}
\end{wrapfigure}
~

Les événements sont organisés dans le but de permettre aux adhérents d'y participer. La table \texttt{participe} est une table représentant cette relation entre un adhérent et un événement 
auquel il participe, elle est donc composée des deux champs \texttt{idAdherent} et \texttt{idEvenement}.

\begin{wrapfigure}[6]{l}{3cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{commente}\\
\hline
idAdherent\\
idEvenement\\
commentaire\\
\hline
\end{tabular}
\end{wrapfigure}
~

Afin de permettre aux adhérents de s'exprimer sur la réussite ou non des événements, ceux-ci doivent pouvoir être commentés afin que le foyer puisse s'améliorer. La table \texttt{commente} est donc une table représentant les commentaires des adhérents à propos des événements. Elle est donc composée des quatre champs \texttt{idAdherent}, \texttt{idEvenement} et \texttt{commentaire}.\\

\begin{wrapfigure}[6]{r}{3cm}
\centering
\begin{tabular}{|c|}
\hline
\textbf{commente}\\
\hline
idAdherent\\
nomJeu\\
note\\
commentaire\\
\hline
\end{tabular}
\end{wrapfigure}
~

Enfin pour connaitre avec précision la qualité d'un événement, un adhérent doit pouvoir noter distinctement les jeux. On peut ainsi savoir quels sont les jeux les moins populaires. La table \texttt{note} est une table représentant les notes et commentaires des adhérents à propos des jeux. Elle se compose donc des quatre champs \texttt{idAdherent}, \texttt{nomJeu}, \texttt{note} et \texttt{commentaire}.\\

Il existe un autre type de relation entre les quatre tables : \texttt{personne}, \texttt{eleve}, \texttt{adherent} et \texttt{bureau}. En effet, de manière intuitive, un membre du bureau est un adhérent, un adhérent est un élève et un élève est une personne. Cette relation permet de ne pas avoir à renseigner dans chaque table des champs communs tels que le nom et le prénom d'une personne. On parle alors d'héritage.

\subsubsection{Schema entité-association}

	À partir de la définition de ces tables, il est possible d'obtenir le schéma suivant. Il permet d'avoir une vision globale de l'organisation des données. On peut ainsi voir directement la composition des tables mais aussi les relations qui les lient.

\begin{figure}[h]
  \centering
  \includegraphics[scale=0.455]{../doc/schema_brut.png}
  \caption{Schéma Conceptuel}
  \label{schemaConceptuel}
\end{figure}
%commentaire_C
%editeur pas nomEditeur ?

En outre ce schéma précise les cardinalités des relations. C'est à dire que chaque entrée de certaines tables ne peut être relier qu'un nombre limité de fois à d'autres entrées de table. Par exemple, un livre a forcément au moins un auteur mais peut en avoir plusieurs. Ou bien un événement utilise au moins un jeu mais peut en utiliser plusieurs.

\subsection{Modèle relationnel}

\subsubsection{Schéma relationnel}

Si les données sont maintenant organisées, elle ne sont pas encore exploitables. En effet, il est pour le moment impossible de distinguer les différentes entrées d'une même table. Par exemple, deux événements se déroulant le même jour au même endroit sont indistinguables. C'est pourquoi il nécessaire d'introduire la notion de clés primaires et étrangères.\\

Si certaines clés sont naturellement associées à certains attributs d'autres ne le sont pas. Par exemple, en considérant que deux jeux ne peuvent avoir le même nom, il suffit de considérer l'attribut \texttt{nomJeu} dans la table \texttt{jeu} comme la clé primaire. Ainsi la connaissance de ce seul attribut, pour une entrée donnée, permet d'accéder à tous les autres.\\

Cependant parfois ces attributs sont absent de la table car ils ne présentent aucun intérêt concret. C'est pourquoi il nécessaire d'insérer un attribut qui aura pour seule fonction de servir de clé primaire. Par exemple, un événement ne possédant pas de nom et pouvant présenter les mêmes caractéristiques qu'un autre événement, il est nécessaire d'introduire l'attribut \texttt{idEvenement}. Celui-ci sera unique à chaque événement.\\

Enfin parfois il est possible que deux attributs permettent de définir l'entrée d'une table. À ce moment là on préférera utiliser ces deux attribus plutôt que d'utiliser un nouvel attribut. Ces couples permettent principalement l'établissement d'une relation entre les deux attributs qui sont des clés étrangères, c'est à dire des clés primaires d'une autre table.\\

Cette explication justifie l'introduction des attributs \texttt{id*} définis dans les tables. On peut alors résumer les relations de clés primaires et clés étrangères par le schéma suivant où chaque table est décrite par tous ses attributs. Un \og\#\fg est placé devant chaque clé étrangère et chaque clé primaire est soulignée.

\begin{figure}[h]
\centering
\begin{tabular}{|ll|}
\hline 
personne & (\underline{idPersonne}, nomPersonne, prenomPersonne)\\
ecriture & (\underline{\#{}idPersonne}, \underline{\#{}ISBN})\\
live & (\underline{ISBN}, titre, prixAchat, dateAchat, empruntable)\\
edite & (\underline{\#{}ISBN}, \underline{\#{}idEditeur})\\
editeur & (\underline{idEditeur}, nomEditeur)\\
eleve & (\underline{login}, \#{}idPersonne, filiere, promotion)\\
adherent & (\underline{idAdherent}, \#{}login, dateAdhesion, dateSeparation)\\
emprunt & (\#{}ISBN, \#{}idAdherent, dateEmprunt, dateRetour)\\%clé primaire ???
bureau & (\underline{idBureau}, \#{}idAdherent, dateEntree, dateSortie)\\
note & (note, commentaire, \underline{\#{}idAdherent}, \underline{\#{}nomJeu})\\
commente & (commentaire, \underline{\#{}idAdherent}, \underline{\#{}idEvenement})\\
participe & (\underline{\#{}idEvenement}, \underline{\#{}idAdherent})\\
evenement & (\underline{idEvenement}, dateEvenement, lieu)\\
utilise & (\underline{\#{}idEvenement}, \underline{\#{}nomJeu})\\
jeu & (\underline{nomJeu}, etat, dateAchat, prixAchat, joueursMin, joueursMax)\\
\hline
\end{tabular}
  \caption{Schéma Relationnel}
  \label{schemaRelationnel}
\end{figure}
\subsubsection{Description et hypothèses}

Un tel modèle implique certaines hypothèses plus ou moins discutables. Nous avons tout de même essayer de concilier l'intérêt du choix des clés primaires avec la considération de certaines situations plus ou moins probables.\\

Ainsi, la clé primaire de la table personne est \texttt{idPersonne}. Ce choix nous permet de modifier ultérieurement le nom ou le prénom. de la personne sans avoir à modifier les liens pouvants exister avec les autres tables. Cela simplifie donc la mise à jour des données.\\

En outre comme pour les personnes, nous avons supposé que deux éditeurs peuvent avoir le même nom. Donc pour les mêmes raisons, un nouvel attribut a été ajouté.\\

Si deux personnes peuvent avoir le même nom et le même prénom, nous considérons que les élèves disposent tous d'un login distinct. En effet, si on se base sur le système d'attribution des logins de l'\bsc{ENSEIRB}, deux élèves ne peuvent avoir le même login. Comme nous ne nous occupons pas de l'attribution des logins, nous pouvons nous contenter de l'attribut \texttt{login} comme clé primaire.\\

La table \texttt{bureau} induit l'ajout de la clé primaire \texttt{idBureau} car cela permet à même adhérent de pouvoir être membre du bureau à deux moments différents. Pour les mêmes raisons la table \texttt{adherent} utilise la clé primaire \texttt{idAdherent}.\\

En outre il convient de remarquer que la table \texttt{emprunt} ne présente aucune clé primaire. Ce choix se justifie par le fait que si on se contente des attributs \texttt{ISBN} et \texttt{idAdherent} comme clé primaires cela empêche un adhérent de pouvoir emprunter deux fois le même livre. Cela nous permettrait aussi de gérer différents exemplaires d'un même livre.\\

Un adhérent ne pouvant noter un même jeu ou commenter un même événement une seule et unique fois, les tables \texttt{note} et \texttt{commente} n'ont pas besoin de l'ajout d'un attribut pour définir une clé primaire. Elles peuvent utiliser respectivement les couples \texttt{(idAdherent, nomJeu)} et \texttt{(idAdherent, idEvenenement)} pour distinguer chaque note et chaque commentaire les uns des autres.\\

Finalement, au delà de l'aspect hiérarchique, la distinction de la table \texttt{adherent} et \texttt{eleve} parait pour le moment inutile. Cependant elle prend tout son sens si l'on souhaite implémenter une autre base de données correspondant à un autre club ou une autre association de l'\bsc{ENSEIRB}. D'autre part cela permet de rendre notre base de données compatible avec une gestion, par exemple administrative, des élèves.
	
\section{Implémentation et interface}
Il a ensuite fallu choisir un langage de programmation afin d'implémenter les requêtes SQL dans une interface graphique simple d'utilisation. Le candidat le plus généralement choisi lorsqu'il s'agit d'intéragir avec une base de données est le PHP, ensuite vient le Java. Dans un soucis de démarquation et afin d'élargir le champ de nos connaissances, il a été décidé d'utiliser le Perl. Ce langage possède une bibliothèque très complète (\emph{DBI}) permettant d'intéragir avec un serveur MySQL, Oracle SQL, PostgreSQL et d'autres, et permet de faire des applications graphiques, par exemple avec la bibliothèque Tk. (figure \ref{interface})

\begin{figure}[h]
  \centering
  \includegraphics[scale=0.35]{screenSGBD.png}
  \caption{Interface du programme}
  \label{interface}
\end{figure}

Quant au Système de Gestion de Base de Données, il a été décidé de se tourner vers MySQL car il est le plus répendu des systèmes libres et gratuits, ce qui permet de travailler sur les machines personnelles.  Maintenant, tournons-nous vers l'implémentation des diverses requêtes indispensables pour satisfaire le cahier des charges.

\subsection{Exploitation des données stockées}
\subsubsection{Mise à jour et insertion des données}
Premièrement, il est présupposé que la base de donnée a été créée. Pour ce faire, il suffit de démarrer le serveur MySQL (ceci dépend de votre système d'exploitation) puis de lancer un script SQL depuis MySQL ou phpMyAdmin (par exemple). Ceci permet de créer une base de données contenant toutes les tables nécessaires tout en fixant les contraintes entre les clés.\\

À partir de maintenant, tout se fera par le biais de scripts Perl en mode graphique. Une fois connecté à la base de données, l'utilisateur n'a plus qu'à utiliser l'interface afin de la mettre à jour. Par mettre à jour, on entend ajouter, supprimer ou modifier des adhérents, des jeux, des membres du bureau ou des évènements. On pourra également commenter des jeux, faire passer un adhérent dans le bureau ou lui faire emprunter un livre par exemple.\\

Dans le but de réaliser ceci, il est demandé à l'utilisateur de préciser ce qu'il veut faire, le cas échéant des informations supplémentaires lui sont demandées, puis ces données sont insérées dans des requêtes SQL envoyées au serveur. Ce dernier va modifier la base de données en conséquence. Il sera principalement utilisé les mots-clefs \texttt{UPDATE}, \texttt{INSERT} et \texttt{DELETE}. Par exemple, pour ajouter un nouvel adhérent la requête utilisée est la suivante.\\

\begin{lstlisting}
  INSERT INTO adherent (dateAdhesion,login) 
         VALUES (str_to_date('$_[5]','%d/%m/%Y'),'$_[4]');
\end{lstlisting}

Dans cet exemple, \texttt{str\_to\_date} est une fonction mySQL servant à reformater une date correctement, \texttt{\$\_[5]} et \texttt{\$\_[4]} sont des variables Perl correspondant à la date et au login. De nombreuses autres requêtes peuvent s'écrire aussi simplement, comme par exemple la suppression d'un membre du bureau. A contrario, d'autres nécessitent de fournir un peu plus d'effort, comme par exemple la fonction de mise à jour d'un adhérent : \\

\begin{lstlisting}
UPDATE personne,eleve,adherent 
        SET nomPersonne = '$quotedArgs[0]', 
            prenomPersonne = '$quotedArgs[1]', 
            promotion = '$quotedArgs[2]', 
            filiere = '$quotedArgs[3]', 
            dateAdhesion = '$date'
        WHERE adherent.login = '$quotedArgs[4]'
          and eleve.login = adherent.login
          and eleve.idPersonne = personne.idPersonne;
\end{lstlisting}
~

Comme on le voit, il est nécessaire d'insérer chacune des données entrées par l'utilisateur. Mais un problème qui peut se révéler dangereux est que l'on ne sait pas ce que l'utilisateur a entré ; pour peu qu'un nom contienne une apostrophe, au mieux la requête échoue, au pire l'intégrité de la base de donnée est compromise (principe de l'injection SQL). Par conséquent, le soin est pris d'échapper tous les caractères spéciaux dans les chaîne de caractères en provenance de l'utilisateur. Heureusement, Perl fournit plusieurs moyens de le faire automatiquement (Modélisé ici par \texttt{\$quotedArgs[X]}).\\

Ensuite, on recherche l'adhérent voulu, ainsi que l'élève et la personne correspondants, afin de les modifier tel que désiré. Il n'y a pas à s'inquiéter du cas où l'on chercherait à modifier un adhérent inexistant : l'utilisateur n'a que peu de libertés sur ce point car il lui est fourni une liste d'adhérents et il n'a qu'à choisir parmi ceux-ci, sans possibilité de prendre une valeur arbitraire.\\

Il est à noter une particularité dans l'implémentation choisie de la base de données : certains champs de certaines entités ne sont pas modifiables. Ces champs correspondent aux clefs primaires. Dans le cas de l'adhérent, cela ne pose pas de problème car la clef primaire est un id incrémenté automatiquement, sans aucune valeur informative, par contre dans le cas du livre l'ISBN représente la clef primaire. C'est un choix sensé car un livre est défini de manière unique par son ISBN, mais cela entraine l'impossibilité de le modifier. En conséquence, si une erreur a été faite lors de sa saisie, il faut recréer une fiche. En contrepartie, on a l'assurance que deux livres identiques ne seront pas créés. \\

% Comment rajouter un adhérent qui est déja auteur ? 
Lors de l'ajout d'un adhérent, il est nécessaire de prendre soin de rajouter au préalable l'élève et la personne correspondants. En effet, dans le cas contraire \emph{MySQL} refuserait d'exécuter la demande car les contraintes ne seraient pas respectées, contraintes liées à la présence de clés primaires. 

%exemple de mise à jour
%interet 
%cle primaire immodifiable
%exemple de mise à jour complexe => nom d'une personne
%							=>auteur d'un livre
%							=>commentaire d'un événement, d'un adhérent 

\subsubsection{Consultations des données}

Une fois que des données ont été rentrées dans la base de données, il est intéressant de pouvoir les consulter. Pour ce faire, il faut pouvoir rechercher parmi les données souhaitées en fixant certains critères. Par exemple, on peut vouloir la liste des Évènements ayant eu lieu à une date précise. Dans ce cas, il n'est pas nécessaire de saisir quoi que ce soit dans les autres champs, car le mot clef \texttt{\textbf{like}} est utilisé. Au lieu de rechercher des valeurs exactes, on fait une recherche par pattern en encadrant ce qui a été saisi du symbole \emph{\%}. Celui-ci à le même rôle que \emph{*} dans les expressions régulières des shells UNIX. De cette façon, un champ vide dans l'interface correspond à une recherche de n'importe quelle chaîne de caractère et '\emph{gan}' permettra de retrouver \emph{Gargantua}. \\

\begin{lstlisting}
    SELECT * from livre, personne, ecriture, editeur, edite 
        where livre.ISBN like '%$quotedArgs[4]%'
        and livre.titre like '%$quotedArgs[0]%'
        and livre.prixAchat like '%$quotedArgs[6]%'
        and livre.dateAchat like '$date'
        and ecriture.ISBN = livre.ISBN
        and ecriture.idPersonne = personne.idPersonne
        and personne.nomPersonne like '%$quotedArgs[1]%'
        and personne.prenomPersonne like '%$quotedArgs[2]%'
        and edite.ISBN = livre.ISBN
        and edite.idEditeur = editeur.idEditeur
        and editeur.nomEditeur like '%$quotedArgs[3]%'
        order by livre.titre;
\end{lstlisting}

 Comme on peut le constater, en plus de la jointure afin d'obtenir l'éditeur et l'auteur, on recherche tous les livres correspondant aux critères souhaités. Le tri par titre est dispensable étant donné que les résultats des requêtes sont ensuite placés dans des tables de hachage non-ordonnées, mais il a été laissé dans un but sémantique.\\

Ensuite, toutes les recherches se ressemblent dans leur principe. On peut dire que la consultation est l'intérêt principal de la base de données, il est donc important que les fonctionnalités soient celles attendues par l'utilisateur. Il a été réalisé un effort afin d'envisager les différents cas d'utilisation et implémenter ceux ayant une probabilité suffisante de se produire.
%exemple de consultation => explication de la requête  
%interet pratique

\subsubsection{Statistiques}

Il peut être intéressant, lorsque la base de données contient suffisamment d'éléments, d'établir des statistiques détaillées sur les habitudes des usagers du foyer. Voici celles qu'il a été décidé d'implémenter : \\

\begin{lstlisting}
SELECT jeu.nomJeu, sum(participant) AS participants 
FROM jeu,utilise,viewEvenement
    WHERE jeu.nomJeu = utilise.nomJeu
    AND utilise.idEvenement = viewEvenement.idEvenement
    GROUP BY jeu.nomJeu
    ORDER BY sum(participant) DESC;
\end{lstlisting}
~

Cette requête permet d'obtenir le classement des jeux selon le nombre total de joueurs ayant participé à une partie. On utilise la fonction \texttt{sum} afin d'obtenir le cumul des participants, en ayant groupé par nom de jeu afin que ce cumul soit par jeu et non le total général. \emph{viewEvenement} est une vue définie comme ci-dessous : \\

\begin{lstlisting}
CREATE OR REPLACE VIEW viewEvenement(idEvenement,participant) as 
  SELECT evenement.idEvenement,count(*) FROM participe, evenement
    WHERE evenement.idEvenement = participe.idEvenement
    GROUP BY idEvenement
    ORDER BY count(*);
\end{lstlisting}
~

Cette vue crée une table de deux colonnes permettant de relier l'ID d'un évènement avec le nombre de participants. On remarque que l'on compte le nombre de participations sans se préoccuper des participants, mais cela ne change rien aux résultats car une participation équivaut à un participant.\\

\begin{lstlisting}
 SELECT personne.nomPersonne,personne.prenomPersonne,adherent.idAdherent,
 count(*) AS participations FROM evenement,adherent,participe,eleve,personne
    WHERE adherent.login = eleve.login
    AND eleve.idPersonne = personne.idPersonne
    AND adherent.idAdherent = participe.idAdherent
    AND participe.idEvenement = evenement.idEvenement
    AND dateEvenement BETWEEN SUBDATE(CURDATE(), INTERVAL 1 YEAR) AND CURDATE()
    GROUP BY adherent.idAdherent
    ORDER BY count(*) DESC;
\end{lstlisting}
~

Cette requête permet d'obtenir le nombre d'évènements auxquels un adhérent a participé depuis un an. Elle utilise la fonction MySQL \texttt{CURDATE} qui permet d'obtenir la date actuelle (le résultat ne sera donc pas juste si la machine n'est pas réglé à la bonne date) ainsi que \texttt{SUBDATE} qui soustrait à une date un certain intervalle (en l'occurrence un an).

\begin{lstlisting}
SELECT livre.titre, livre.ISBN, count(*)/12 AS moyenne FROM emprunt, livre
    WHERE emprunt.ISBN = livre.ISBN
    AND dateEmprunt BETWEEN SUBDATE(CURDATE(), INTERVAL 1 YEAR) AND CURDATE()
    GROUP BY livre.ISBN;
\end{lstlisting}
~

Celle-ci permet de calculer la moyenne sur un an du nombre d'emprunts par mois des différents livres. On peut alors connaitre les livres les plus populaires auprès des adhérents et ainsi investir le budget de manière appropriée.

%toutes les requêtes de stats => explication
%interet pratique

\subsection{Interface Perl}
\subsubsection{Système d'onglet}

Il a été choisi d'utiliser la bibliothèque \emph{Tk} afin de gérer l'interface graphique. Celle-ci permet aisément de créer des onglets, étant donné les nombreuses possibilités de l'application il est apparu avisé d'en utiliser afin de séparer les différents sections gérées. Dans chacun de ces onglets il est possible de rechercher et/ou modifier un item, ou d'afficher des statistiques.\\

Il a été choisi de différencier chacun des champs de recherche en fonction de leur cible, plutôt que de faire un seul champ général qui agirait comme un moteur de recherche sur l'ensemble de la base de donnée. Ainsi, on gagne en clarté et en pertinence des résultats, ainsi qu'en vitesse de recherche.\\

De nombreux tests de cohérence sont réalisés en amont de l'accès à la base de données, dès la saisie, par exemple afin de vérifier que la date de sortie du bureau est postérieure à la date d'entrée. Néanmoins, il n'est pas effectué de tests avancés afin de vérifier la correction ou l'adéquation avec les spécifications des données entrées. Le programme ne bugue pas, seul un avertissement dans le terminal est affiché.
%Possibilité de distinguer des entités principales (même si interdépendantes)
%Description de chaque onglet (Adherent et Bureau ensemble) 
%%%Livre explication d'un seul champ auteur//Evenement -> j
\subsubsection{Récupération et exploitation des données}
Lorsque l'on envoie une requête SQL au serveur, celui-ci doit renvoyer une table, mais quelle est sa représentation en mémoire ? Le module DBI nous offre principalement deux choix : 
\begin{itemize}
  \item Un tableau de références de tables de hachages
  \item Un tableau multidimensionnel\\
\end{itemize}

La solution principalement adoptée est le tableau de tables de hachage car il autorise un accès facilité aux différentes colonnes et lignes de la table en ayant une syntaxe haut-niveau. On accède à une ligne en utilisant les clefs primaires, on accède à une colonne grâce à son nom. L'inconvénient est que Perl, afin d'accélérer la vitesse de parcours, utilise un tri qui ne respecte pas l'ordre des lignes fourni par MySQL. Ainsi, l'utilisation de \texttt{order by} n'a pas d'effet, il faut donc faire trier les lignes par Perl.\\

L'autre solution a été utilisée ponctuellement pour récupérer une valeur particulière. Par exemple lorsqu'on cherche à récupérer l'identifiant d'un adhérent ayant un login, on utilise ce type tableau car dès qu'on souhaite récupérer plus d'une valeur, il devient fastidieux de déterminer à quelle indice correspond chaque colonne. En effet, l'ordre dans lequel sont placés les colonnes est indéterminé même si les lignes sont triés. Lorsque qu'il n'y qu'une colonne se problème ne se pose pas. 

%Table de hashage=>intérêt des clés primaire => accès facile aux champs mais pas ordonné
%Tableau simple => difficile d'accéder à un attribut précis mais ordonnés

\section{Problèmes et améliorations}
\subsection{Problèmes rencontrés}
\subsubsection{Gestion de l'interface}

Si notre interface présente des avantages, elle possède aussi des défauts. Par exemple il est difficile d'implémenter un nombre de champs variable, c'est pourquoi nous avons choisis de se restreindre à un seul champ auteur dans l'onglet livre et d'un champ jeu dans l'onglet événement. Cela permet d'ajouter un livre avec un auteur. Si l'on souhaite en ajouter d'autres, il faut entrer les données directement dans la base de données. Plusieurs solutions ont été envisagées. Par exemple l'utilisateur pourrait ajouter des auteurs à un livre après qu'il ait été ajouté à la base de données. Cette solution reste fastidieuse car l'on doit ajouté un à un chaque auteur du livre.\\

En outre, la gestion des membres du bureau et des adhérents est très redondante. En effet, certaines données sont répétées dans les deux onglets. On peut par exemple connaître, à partir des deux onglets, les membres du bureau actuellement. Cependant la quantité des données présente dans chacune des tables est trop importante pour autoriser la fusion des deux onglets. C'est pourquoi nous avons choisi de garder les deux onglets.\\

Notre interface présente un autre désavantage. Elle ne permet pas l'actualisation dynamique des données. C'est à dire qu'il est nécessaire de recharger l'interface pour qu'elle relance les requêtes et puisse alors avoir accès aux nouvelles informations. Par exemple, si l'on emprunte un livre à l'aide de l'interface, l'onglet statistiques ne sera mis à jour que lorsque l'interface sera relancée.
	
\subsubsection{Gestion des dates}

 Un point particulier est le format des dates. En France est utilisé JJ/MM/AAAA ou JJ/MM/AA. Ici le premier format est imposé de manière stricte, i.e. Janvier s'écrit \emph{01} et pas \emph{1} ; ainsi une date correctement formatée s'écrit \emph{23/07/1991}. Cette contrainte facilite le travail, même s'il aurait été possible d'envisager de traiter tous les cas. À noter qu'il existe une exception : on peut ne renseigner que l'année afin d'obtenir par exemple la liste de ceux ayant adhéré au cours de 2003.\\

Dans MySQL, le format imposé est \emph{AAAA-MM-JJ}. Ce format est particulièrement pratique car il permet de faire concorder l'ordre lexicographique et l'ordre chronologique, mais il est peu intuitif pour qui n'y a pas été habitué. C'est pourquoi il est systématiquement fait une conversion entre le format utilisé dans l'interface et celui utilisé par le système de gestion des bases de données.\\

Les champs correspondant à des dates ont comme valeur par défaut \texttt{NULL} (en réalité, pour une date cela correspond à \emph{0000-00-00}) à l'exception du champ \emph{dateSortie} dans bureau. En effet, il est possible de rechercher les adhérents qui, à une date précise, faisaient partie du bureau. Pour ce faire, il est vérifié si la date est comprise entre \emph{dateEntree} et \emph{dateSortie}. Avec \emph{dateSortie} valant \texttt{NULL}, cette comparaison à l'aide du mot-clef \texttt{BETWEEN} ne pourrait fonctionner pour les membres du bureau actuels, ceux n'ayant pas de date de sortie. Par conséquent, la valeur par défaut pour ce champ à été mise à \emph{9999-12-31}.

%Gestion des dates => problème de standard (anglo-saxon, français)
%				=> date non défini (sortie)

\subsection{Extensions possibles}
\subsubsection{Gestion de la base de données}

Notre gestion de la base de données peut être améliorée. Par exemple, en ce qui concerne les livre, il peut être intéressant d'ajouter un résumé des livres ou une image représentant la troisième de couverture du livre. Cela permettrait à l'adhérent d'avoir plus d'information pour choisir son livre sans avoir à consulter le livre. En outre, il serait très utile de pouvoir gérer plusieurs exemplaires de livre. En effet, cela se produit très souvent, surtout pour des livres très prisés. À ce moment là, il faudrait introduire un attribut \texttt{nbExemplaire}, qui donnerait le nombre d'exemplaire pour un livre donné.\\

De manière similaire, le système de gestion des événements pourrait être amélioré. En fait comme les livres, il serait intéressant de pouvoir avoir plusieurs exemplaires d'un même jeu. Dans notre approche le nombre d'exemplaire de chaque jeu est infini car des événements peuvent utiliser simultanément les mêmes jeux au même moment. Enfin, pour permettre de choisir le jeux avec plus de précision, il serait cohérent de pouvoir ajouter les règles du jeu. On pourrait ainsi savoir si un jeu est adapté à un événement donné.\\

Au-delà de ces simples améliorations, notre gestion des personnes, élèves et adhérents permet une compatibilité avec la gestion d'autres types de clubs ou associations. On pourrait par exemple ajouter une table \texttt{club} qui contiendrait le nom de chaque association de l'\bsc{ENSEIRB}. La table \texttt{adherent} deviendrait alors une liaison entre \texttt{eleve} et \texttt{club}.

\subsubsection{Amélioration de l'interface}

Notre interface est pour le moment destinée à être utilisée par les responsables du foyer car l'utilisateur peut ajouter ou supprimer des données importantes. Pour faciliter la gestion du club, les adhérents devrait pouvoir aussi utiliser l'interface. Il faudrait donc ajouter un système d'authentification qui permettrait d'accéder ou non à certaines fonctionnalité de l'interface. Enfin un système d'inscription permettrait de rendre l'utilisation de l'interface totalement autonome.\\

D'autre part, pour permettre son accès plus facile, il aurait été judicieux de pouvoir y avoir accès par internet. L'interface aurait pu alors se faire avec les technologies HTML et PHP qui se porte bien aux fonctionnalités proposées comme le système d'espace membre. Ces autres technologies permettraient d'avoir accès à d'autre moyens de parcours plus ergonomiques car on se retrouve rapidement avec des fenêtre avec un nombre important de boutons. Enfin, d'un point de vue purement visuel, l'apparence de notre programme se révèle plutôt austère et peu attrayante. L'utilisation d'images et de couleurs serait un point positif non négligeable.

\section*{Conclusion}
\addcontentsline{toc}{section}{Conclusion}

À travers la mise en place de ce projet, nous avons pu découvrir les grandes étapes de la mise en place d'une base de données et comprendre la difficulté que peut représenter l'implémentation de bases bien plus importantes comme celles des réseaux sociaux mondialement utilisés. En outre cela nous a permis de consolider les connaissances théoriques abordées en cours telle que l'établissement d'un modèle relationnel ou entité-association.\\

Une autre partie du travail a consisté à pourvoir accéder et exploiter les données contenues dans la base de manière la plus simple possible. En effet, une base de données est le plus souvent vouée à être utilisée par des personnes aux connaissances différentes. Il faut donc permettre à l'utilisateur le plus novice d'accéder aux informations qu'ils souhaite. C'est pourquoi nous avons essayé d'implémenter une interface avec un minimum de fonctionnalités.\\

Finalement ce projet a été l'occasion de se placer dans la peau de futurs developpeurs qui sont sont continuellement amenés à interagir avec des base données. Les connaissances que nous avons acquises sont donc indispensables pour toute conception d'un projet. 

\end{document}
